PC-SIG: World of Education/PC-SiG's World of Education.iso/run/3312/chpt3.txt

Text File | 1992-03-07 | 22KB | 505 lines

                                                            page 3-1

              CHAPTER 3:  CELLS, VARIABLES AND FUNCTIONS


This chapter will outline in detail how FREE & EASY does calculations.
Thus we will look at how cells and variables are used and how they work
together.  All features looked at in this chapter are found in the
"Cell" pull-down menu.


How Calculations Are Done

FREE & EASY does all its calculations in cells.  If you understand how
a spreadsheet uses cells, then you have a good idea of how cells work
in FREE & EASY.  In a spreadsheet, you are locked into a grid of rows
and columns of cells.  With FREE & EASY, however, the situation is
different.  FREE & EASY has a sheet onto which you add text wherever
you wish.  And on the sheet you can also add cells wherever you need
them.  This allows you to add descriptive text in the vicinity of the
cells to describe what calculations your cells are doing.

Just as a spreadsheet's cells have contents in the form of some
mathematical expression, the cells of FREE & EASY have contents in the
form of a mathematical expression.  The cell contents can contain
numbers, variables, the value of another cell and several types of
functions.

When the contents of the cell are evaluated, the cell then has a value.
This value is displayed on the sheet.  For each cell, the format in
which that value is displayed can be modified to suit the situation.

With a spreadsheet, a cell's contents can refer to the value of a
variable by name, but that variable can only be another cell.  In FREE
& EASY, a variable can be defined anywhere in the sheet with an "="
sign.  For example, suppose in the text of the sheet, the following is
found:

                 distance = 4.36

The variable "distance" can now be used in any of the cell contents,
and when FREE & EASY encounters the variable "distance" in the cell
contents when calculating the cell value, it will substitute the value
4.36.  Like a spreadsheet, any cell in FREE & EASY can be given a name,
and that name can be used as a variable by any other cell.  In this
case, when FREE & EASY encounters the cell name when evaluating the
cell contents it will substitute the value of the cell.

In the cell contents, FREE & EASY also allows you to refer to the value
at a location in the sheet.  In this case, there can be a number at
this location or another cell.  FREE & EASY will substitute this value
when evaluating the cell.  There are also functions (called range
functions) in FREE & EASY which use a range (a user defined rectangular
area of the sheet) and do calculations on all values found in that
range.

As you can see, these features give you tremendous flexibility to
construct sheets of considerable complexity.  Yet you also have the








                                                            page 3-2

ability to add cells where you need them, to add descriptive text, and
to add variables where desired.  This allows you to keep your sheet
easy to follow and understand.


Adding and Removing Cell(s) to the Sheet

There are several ways to add cells to the sheet.  A single cell can be
added with the "Add/modify cell ...", "cell Contents ..." or "cell Name
..." menu items.  The cell is added at the present cursor position.  If
the cursor is already on a cell when one these menu items is called,
that cell will be modified rather than a new cell added.

You can remove a cell from the sheet by placing the cursor on the cell
you wish to remove and selecting the "Remove cell" menu item.
(control-R is the short cut key for this).

Blocks of cells can also be moved, copied and deleted.  This is not
covered in this chapter but in chapter 4 on Editing.

The cells on the sheet are highlighted.  Several cells at once can be
added to the sheet with the "Table of cells ..." menu item.


Cell Contents

When you enter the cell contents, if you are adding a new cell to the
sheet, the initial cell contents that you will see are those of the
default cell.  The default cell is used extensively by FREE & EASY and
more is said on it later.  If you are modifying a cell, then the
initial cell contents you will see are the present cell contents of the
cell that you are modifying.

The cell contents can be any valid mathematical expression up to 200
characters long.  It is unlikely, however, that you will ever have cell
contents this long.  It is often better to break up a complicated
expression into several cells.  When you enter the cell contents, FREE
& EASY removes all spaces, converts functions to upper case, and checks
the syntax.  If there is an error in the syntax, FREE & EASY displays a
message indicating what the error is and will not accept what you have
typed.  Remember, if you get an error message, you can always press
"F1" to get further explanation.  Appendix B has a full listing of
syntax errors.

When evaluating cell contents, FREE & EASY follows the rules of
algebra, i.e. the cell contents are evaluated left to right with the
following preference of operators:

2^3              raise to power             =8
2*3              multiply                   =6
2/3              divide                     =1.5
2+3              add                        =5
2-3              subtract                   =-1

When you enter the cell contents, FREE & EASY can also give you a
warning.  This usually means that even though the syntax is correct,







                                                            page 3-3

there can be an error when evaluating the cell contents.  If this
happens, you should check the what cell contents you have typed in to
make sure that you have entered what you intended.  Appendix C has a
complete list of warnings.

Brackets ("(" and ")") can be used to change the precedent of operator
evaluations.  You cannot use the curly brackets or the square brackets.
Of course, the brackets must be used algebraically correctly or a
syntax error will be given.

FREE & EASY has several types of functions which can be used.  All
functions have three letters and are upper case.  Remember that
functions are converted to upper case when you enter them.  Appendix A
lists all functions available, and chapter 6 covers functions in
greater detail.  The argument of a function must be in brackets.  i.e.
SIN(30) is acceptable but SIN 30 is not acceptable.

The cell contents can also contain variables.  If two variables are
defined in the sheet as follows:

                length=4        width=3

then if the cell contents were "length*width" the cell value upon
evaluation would be 12.

The contents of a cell can refer to a location on the sheet.  There
must be a valid number or cell at that location; otherwise an
evaluation error will be given for that cell.  If you want to refer to
the sheet location at row 15 and column 126 (note that the right side
of the prompt bar shows the cursor location in row:column format) then
the following found in the cell contents would show that location:

             !0015!0126

Note that the row and column locations are each preceded by the '!'
character and each are four digits.  (Preceding 0's are added if
necessary)  If this syntax is not followed exactly, a syntax error will
be given when you are trying to enter the cell contents.

The location referred to is relative to the cell containing the
location. This means that if the cell is moved (with one of the
editing features for example) the location referred to moves
correspondingly.  The same applies to entering cell contents for the
default cell.

It is possible to make the location referred to absolute by using the
'$' character instead of '!'.  Thus for the example above, if the
location is to be absolute, the following would appear in the cell
contents:

             $0015$0126

In this case if the cell containing this location is moved the location
does not change.  Of course, if the cell is never moved it makes no
difference whether the location referred to is relative or absolute.

It is also possible to have the row location absolute and the column








                                                            page 3-4

location relative (or vice versa).  For example:

             $0015!0126

In this case if the cell is moved the row referred to remains the same
and the column changes.  There are several sample sheets included in
the FREE & EASY package where examples of this are used.

It is also possible to refer to a location on the sheet other than the
sheet in which the cell occurs.  For example:

             &FILE1.FRE$0015$0126

In this case reference is made to row 15 and column 126 on the sheet
named FILE1.FRE.  The '&' character must precede the file name.  For a
location on another sheet, the location must be absolute.  If the
location is entered as relative FREE & EASY will change it to absolute.

When entering cell contents and you want to input a sheet location, the
location can be typed in as described above.  However, an easier way is
to press the '\' key while typing in the cell contents.  At this point
the menu will disappear.  The cursor control keys are used to bring the
cursor to the location you wish to refer to in the cell contents.  The
F5 and F6 keys can be used to refer to a location in another sheet.
Press Enter to accept that location as relative, press
control-Enter to accept that location as absolute or press Escape to
cancel.  You will come back to the menu you just left.  FREE & EASY
will have added the location you have chosen (if you did not press
Escape) in the correct format to the cell contents.

If you press the '\' key while the cursor in the cell contents is on a
valid sheet location, then the cursor will start out at that location.
Otherwise it will start out at the location of the cell whose contents
you are entering.

When entering the cell contents, you can use the key combination
control-P to enter the symbol for PI.  When evaluating a cell and this
symbol is encountered, the value 3.14159... is used.

After a cell has been successfully evaluated, then the cell's value is
that evaluated value.  The cell value is then displayed on the sheet in
the format for that cell.  If there is an evaluation error, the cell
value is set to 0 and this value is displayed.


Variables

Variables are defined on the sheet with an equal "=" sign.  The
variable name is to the left and variable value to the right of the
equal sign.  Any number of spaces may be between the equal sign and
name and value.  All must be on the same line.  Once the variable has
been defined, it can be used in the contents of any cell.  You should
give variables meaningful names as this will make your calculations
easier to follow.









                                                            page 3-5

When you enter the cell contents and use a variable name, FREE & EASY
checks to ensure that you have used a valid name.  The name must start
with a letter and contain only letters and numbers or the underscore
character (no spaces allowed).  Names are case sensitive, i.e..  FREE &
EASY treats var1, VAR1 and Var1 all as different variables.  The
maximum length is 20 characters.  If an invalid name is used, an error
message is given.

When evaluating a cell and FREE & EASY encounters a variable name, it
finds the variable on the sheet then looks for its defined value.  This
value must be a valid number.  The number must be between 1E300,
-1E300, 1E-300 and -1E-300, and must have the correct syntax.  For
example, the number 30g6 is not an acceptable number.  If the number is
not acceptable, FREE & EASY will give an error message.  The same rules
for a number assigned to a variable also apply to numbers in the cell
contents.


Cell Name

Any cell can be given a name.  The cell name must start with a letter.
Then any combination of letters or digits or the underscore character
can follow.  The maximum length is 20 characters.  The cell name can be
used to refer to the value of that cell in the contents of another
cell.  Both a cell and a variable can have the same name (although this
is not recommended).  When a cell is being evaluated and a name is
encountered, it can be a cell name or a variable name.  FREE & EASY
will search until it finds a value (either cell or variable) for that
name.  Whether it starts searching for a cell name or variable name
first depends on the evaluation defaults.  These are discussed in
chapter 6.  The cell name can be adjusted on the "Add/modify cell" menu
item or the "cell Name" menu item.


Format

FREE & EASY allows you to use a variety of formats to display the value
of the cell on the sheet.  When a new cell is added to the sheet, the
format in the default cell is used.  This can be changed, however, with
the "Add/modify ..." menu item.  At the bottom of this menu, the cell
is shown as it will appear on the sheet.  You can use this to adjust
the format to give the appearance you want before you add or modify the
cell.

The following format items can be adjusted:

    size:  This is the size of the cell on the sheet.  The cell can be
           from 1 to 21 characters wide.

    decimals:  This is the number of decimal places the display has.
           It can be 0 to 14.  FREE & EASY keeps track of 14
           significant figures.

    style:  There are four choices here:
           floating point:  This is probably the one you will use most
           often;








                                                            page 3-6

           exponential:  This is scientific notation and is useful for
                very large or very small numbers;
           integer:  This display shows no decimal point or decimal
                places;
           free:  For this style, FREE & EASY makes the choice of
                floating point or exponential depending on the value of
                the cell.

     justify:  The display value can be justified at the left or right
           of the cell.

     plus sign:  A leading plus sign can be added to the number if it
           is positive.  If the number is negative, a leading minus
           sign will always be added.

     zeros:  Leading zeros can be added to fill up the entire cell
           space.

The format chosen in no way affects the value of the cell.  It is
possible that the cell value and what is displayed in the cell are not
the same.  For example, the cell value could be 2.4678.  If the decimal
is set to two, the display will be 2.47.

When you are using a cell name or location of a cell in the cell
contents, FREE & EASY uses the actual value of the cell and not the
value displayed in its calculations.  It is also possible that the
various format items you set are not compatible.  For example, if size
is set to 10, but the decimal is set 11, the display value cannot fit
into the cell.  In this case, the cell display is filled with "*'s".
Again the cell value is not affected.


Default Cell

FREE & EASY has a default cell which is used in many situations.  We
have already seen it used in adding cells to the sheet if the cell
contents or format are not adjusted.  The settings in the default cell
can be changed with the "set cell Defaults ..." menu item.  There is no
default cell name.  This is because FREE & EASY does not allow two
cells in the same sheet to have the same name.  With this exception,
the items in "set cell Defaults ..." are adjusted the same way as in
the "Add/modify cell ..." menu.


Table of Cells

There are times when you might want to add a block of cells to the
sheet at one time.  It would be very tedious to add them one at a time,
so FREE & EASY allows you to add a block of cells at one time using the
"table of Cells" menu item.  Before adding the cells, you can adjust
the number of rows and columns of cells to add as well as the spacing
between the rows and columns.  The top left corner of the block of
cells will be at the present cursor position.

If the table of cells runs off the bottom or edge of the sheet, an
error message is given and the cells will not be added.  As well,









                                                            page 3-7

before the cells are added all text and cells within the area of the
sheet where the cells will appear are removed.  Even cells which
straddle the border of the block of cells will be removed.  if text or
cells must be removed in order to add the table of cells, FREE & EASY
will give a "data will be lost" warning asking for confirmation that
you wish to continue.

The contents and format of the cells added will be that of the default
cell.  Thus before adding a table of cells, ensure that the default
cell has the contents and format you want for your table of cells.


Table of Data

As well as adding a table of cells, you can also add a table of data
(or numbers) to the sheet with the "taBle of data" menu item.  As the
"Table of cells" menu, you can adjust the rows, columns and spacing.
Again FREE & EASY checks that the table will fit on the sheet, erases
any text and cells necessary to make room for the table and warns you
if this will be done.

You can also adjust the initial value for the table (the value of the
number in top left corner) as well as the increment between the values.
Both of these must be valid numbers.  FREE & EASY will tell you if they
are not.

The format used to add the table of data is the format of the default
cell.  Before adding the table of data, ensure that the format is what
you want.  It is possible if the format is not what you want that you
will not get the number you want in the table.  For example, if the
initial value is 1.00 and the increment value is 0.01, and the default
cell has decimal set to 1, the first three entries in the table of data
will be 1.0, 1.0 and 1.0.  (and not 1.00, 1.01 and 1.02)


Sheet Locations in Cell Contents

You have already seen how you can refer to a location on the sheet in
the cell contents.  The location is referred to by a row and column
number.  You can refer to a cell or number at this location.  (FREE &
EASY will check for both when evaluating the cell.)  Remember that
the location referred to can be relative or absolute.  If the location
is relative and you move the cell, this location changes in the same
way that you moved the cell.  (e.g. If the cell moves down 2 and right
3, this location moves down 2 and right 3 as well.)  The contents of
the default cell also behave like this.  You can move the cursor around
and call up the default cell and see how a sheet location changes (if
the location is relative).  If you add a table of cells and the default
contents include a sheet location which is relative, this location will
be different for every cell in the table.

If you want to refer to a specific cell or specific location on the
sheet regardless of how a cell is moved you can use an absolute address
or refer to that cell by its name (and give it a name if it does not
have one.)  You may also assign a variable to a number at that location
and use the variable name for that number in the cell contents.








                                                            page 3-8

Remove All Cells

The "rEmove all cells" menu item will erase every cell on the sheet.
This can be useful if you want to start from scratch on adding cells to
a sheet.  FREE & EASY asks you to confirm that you really want to do
this before proceeding.


Format Highlighted Area

Sometimes you might want to change the format of a group of cells.  The
format of one cell can be changed with the "Add/modify cell" menu item
but if there are several cells to change, this can be tedious.  Thus a
whole group of cells can have their format changed with the "Format
highlighted area" menu item.

To use this menu item, first you must make sure the default cell has
the format you want. You then highlight the area of the sheet
containing the cells where you want to change the format.  To do this,
use the "Highlight" menu item in the "Edit" pull-down menu.  (You can
also start this with control-H.)  More details on highlighting are
given in chapter 4.  You can also simply follow the prompt bar to use
the highlighting features.  Once the area has been highlighted, choose
the "Format highlighted area" menu item.  All cells completely within
the highlighted area will have their format changed to the format of
the default cell.

There are two situations where it is possible to lose data in doing
this procedure.  If you are increasing the size of the cell and it will
overlap text, that text will be erased, or if it will overlap another
cell, that cell will be removed (cells cannot overlap).  If by
increasing the size of a cell, you force the cell off the edge of the
sheet, that cell will be removed.  If any of these situations occur
when formatting a highlighted area, FREE & EASY will give a "data will
be lost" warning and ask for confirmation about whether you want to
proceed.